USE Core
GO
IF OBJECT_ID('fDateFormat') IS NOT NULL 
BEGIN 
    DROP FUNCTION fDateFormat
    IF OBJECT_ID('fDateFormat') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION fDateFormat >>>'
    ELSE 
        PRINT '<<< DROPPED FUNCTION fDateFormat >>>'
END
GO
/*
    Based on dbo.FormatDateTime at http://www.aspfaq.com/show.asp?id=2460.
*/
CREATE FUNCTION dbo.fDateFormat
(
        @cmdName varchar(32) = NULL
    ,   @dt      datetime    = NULL
 )
RETURNS varchar(64)
AS
BEGIN
    DECLARE @dtStr VARCHAR(64)

    IF @cmdName = 'LONGDATE'
    BEGIN
        SET @dtStr = DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
    END

    IF @cmdName = 'LONGDATEANDTIME'
    BEGIN
        SET @dtStr = DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
        + SPACE(1) + RIGHT(CONVERT(CHAR(20), 
        @dt - CONVERT(DATETIME, CONVERT(CHAR(8), 
        @dt, 112)), 22), 11)
    END

    IF @cmdName = 'SHORTDATE'
    BEGIN 
        SET @dtStr = LEFT(CONVERT(CHAR(19), @dt, 0), 11)
    END
 
    IF @cmdName = 'SHORTDATEANDTIME'
    BEGIN
        SET @dtStr = REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0), 
            'AM', ' AM'), 'PM', ' PM') 
    END

    IF @cmdName = 'UNIXTIMESTAMP'
    BEGIN
        SET @dtStr = CAST(DATEDIFF(SECOND, '19700101', @dt) AS VARCHAR(64)) 
    END

    IF @cmdName = 'YYYYMMDD'
    BEGIN
        SET @dtStr = CONVERT(CHAR(8), @dt, 112) 
    END

    IF @cmdName = 'YYYY-MM-DD'
    BEGIN
        SET @dtStr = CONVERT(CHAR(10), @dt, 23) 
    END

    IF @cmdName = 'YYMMDD'
    BEGIN
        SET @dtStr = CONVERT(VARCHAR(8), @dt, 12) 
    END

    IF @cmdName = 'YY-MM-DD'
    BEGIN
        SET @dtStr = STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12),5, 0, '-'), 3, 0, '-')
    END

    IF @cmdName = 'MMDDYY'
    BEGIN
        SET @dtStr = REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0)) 
    END

    IF @cmdName = 'MM-DD-YY'
    BEGIN
        SET @dtStr = CONVERT(CHAR(8), @dt, 10) 
    END

    IF @cmdName = 'MM/DD/YY' 
    BEGIN
        SET @dtStr = CONVERT(CHAR(8), @dt, 1) 
    END

    IF @cmdName = 'MM/DD/YYYY'
    BEGIN
        SET @dtStr = CONVERT(CHAR(10), @dt, 101) 
    END

    IF @cmdName = 'MM/dd/yyyy hh:mm tt'
    BEGIN
        SET @dtStr = CONVERT(CHAR(10), @dt, 101)
            + SPACE(1)
            + LTRIM(SUBSTRING(CONVERT(VARCHAR(20), @dt, 22), 10, 5) 
            + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))
    END

    IF @cmdName = 'DDMMYY'
    BEGIN
        SET @dtStr = REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0)) 
    END
    IF @cmdName = 'DD-MM-YY'
    BEGIN
        SET @dtStr = REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-') 
    END

    IF @cmdName = 'DD/MM/YY'
    BEGIN
        SET @dtStr = CONVERT(CHAR(8), @dt, 3) 
    END

    IF @cmdName = 'DD/MM/YYYY'
    BEGIN
        SET @dtStr = CONVERT(CHAR(10), @dt, 103) 
    END

    IF @cmdName = 'HH:MM:SS 24'
    BEGIN
        SET @dtStr = CONVERT(CHAR(8), @dt, 8) 
    END

    IF @cmdName = 'HH:MM 24'
    BEGIN
        SET @dtStr = LEFT(CONVERT(VARCHAR(8), @dt, 8), 5) 
    END

    IF @cmdName = 'HH:MM:SS 12'
    BEGIN
        SET @dtStr = LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11)) 
    END

    IF @cmdName = 'HH:MM 12'
    BEGIN
        SET @dtStr = LTRIM(SUBSTRING(CONVERT(
        VARCHAR(20), @dt, 22), 10, 5) 
        + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3)) 
    END

    IF @cmdName = 'RFC822'
    BEGIN
        IF LEN(DATENAME(dd,@dt)) > 1
        BEGIN
            SET @dtStr = SUBSTRING(DATENAME(dw,@dt),1,3)
            + ', '
            + DATENAME(dd,@dt)
            + ' '
            + SUBSTRING(DATENAME(m,@dt),1,3)
            + ' '
            + DATENAME(yy,@dt)
            + ' '
            + CONVERT(CHAR(8), @dt, 8)
            + ' +0000'
        END
        ELSE
        BEGIN
            SET @dtStr = SUBSTRING(DATENAME(dw,@dt),1,3)
            + ', 0'
            + DATENAME(dd,@dt)
            + ' '
            + SUBSTRING(DATENAME(m,@dt),1,3)
            + ' '
            + DATENAME(yy,@dt)
            + ' '
            + CONVERT(CHAR(8), @dt, 8)
            + ' +0000'
        END

    END

    RETURN @dtStr
END
GO
GRANT EXECUTE ON fDateFormat TO public
GO
IF OBJECT_ID('fDateFormat') IS NOT NULL 
    PRINT '<<< CREATED FUNCTION fDateFormat >>>' 
ELSE 
    PRINT '<<< FAILED CREATING FUNCTION fDateFormat >>>' 
GO
